package cn.com.kh;

import java.io.*;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;

public class MySQLSlowLogParser {
    private static int totalSlowSQL;    //总的慢SQL条数

    private static final String INSERT_STM = "insert";
    private static final String UPDATE_STM = "update";
    private static final String SELECT_STM = "select";

    private static final List<String> records = new ArrayList<>();    //存单条记录的集合
    private static final List<LogStatement> logs = new ArrayList<>();   //存筛选字段后的记录

    public static void main(String[] args) {
        // Scanner scan = new Scanner(System.in);

        System.out.println("请输入要解析的 MySQL/MariaDB 慢SQL的全路径：");

        // if (scan.hasNextLine()) {
        // String filePath = scan.nextLine();  //读取文件路径
        String filePath = "C:/Users/lijun/Desktop/待处理/mysql_slowsql.log";  //读取文件路径
        System.out.println(filePath);
        parse(filePath);    //解析对应文件
        //}
        getResult();    //提取每条记录的关键信息
        sortResult();       //将结果进行排序
        printResult();      //打印结果
    }

    private static void parse(String filePath) {
        System.out.println("开始解析：" + filePath);
        InputStream is = null;
        Reader reader = null;
        BufferedReader bufferedReader = null;

        try {
            //以缓冲流的的方式读取数据
            is = new FileInputStream(new File(filePath));
            reader = new InputStreamReader(is, "utf-8");
            bufferedReader = new BufferedReader(reader);

            String singleSQL = "";      //用来存完整的单条慢SQL记录
            String line;

            while ((line = bufferedReader.readLine()) != null) {
                if (line.startsWith("# Time:")) {   //当前行以“# Time:”开头的情况
                    covertAndAddStatement(singleSQL);     //那么之前的语句为一条完整记录，将singleSQL进行转换
                    while (line.contains("  "))      //将多个空格保留为1个
                        line = line.replace("  ", " ");
                    records.add(line);              //直接将当前行的时间存入记录的集合，因为有的记录共享一个时间
                    singleSQL = "";                 //处理完前一条后，要重新拼记录，令singleSQL为空
                } else if (line.startsWith("# User@Host")) {    //当前行以“# User@Host”开头的情况
                    covertAndAddStatement(singleSQL);     //那么之前的语句也为一条完整记录，将singleSQL进行转换
                    singleSQL = line;               //当前行作为新的记录的开头
                } else {
                    singleSQL += line + " ";       //不满足前两个，则直接把当前行加入，作为单条记录的一部分
                }                                   //末尾加空格是为了将两行之间以空格隔开

            }
            //还要处理最后一句，因为最后一条记录的后面没有“# Time:”或“# User@Host”，while循环不会执行到最后一句
            covertAndAddStatement(singleSQL);
        } catch (IOException e) {
            System.err.println("Error:" + e);
        } finally { //释放资源
            try {
                if (bufferedReader != null)
                    bufferedReader.close();
            } catch (IOException e) {
                System.err.println("Error:" + e);
            }
            try {
                if (reader != null)
                    reader.close();
            } catch (IOException e) {
                System.err.println("Error:" + e);
            }
            try {
                if (is != null)
                    is.close();
            } catch (IOException e) {
                System.err.println("Error:" + e);
            }
        }
    }

    private static void covertAndAddStatement(String statement) {
        if (statement.equals(""))    //空字符串直接不处理
            return;
        //去掉“#”号
        statement = statement.replace("#", " ");
        //多个空格替换为1个
        while (statement.contains("  "))
            statement = statement.replace("  ", " ");
        records.add(statement);
    }

    private static void getResult() {    //遍历单条记录，处理结果，加入结果集
        String date = "";       //有多条记录的时间相同，所以要把时间放循环体外，方便为多条记录赋值
        String time = "";
        for (String record : records) {

            if (record.contains("# Time")) { //若当前的record为时间，则更新即将处理的记录的时间
                String[] tmp = record.split(" ");
                date = tmp[2].split("T")[0];
                time = tmp[2].split("T")[1].substring(0,8);
                if (time.length() < 8)    //长度不足用0补充占位
                    time = "0" + time;    //例如: 3:24:48替换为03:24:48
                continue;
            }
            LogStatement log = new LogStatement(date, time);

            totalSlowSQL++;    //慢SQL计数加一

            //每条记录都有timestamp，按其分割字符串更快找到SQL语句
            String[] tmp = record.split("timestamp");
            //前半段可以得到相关信息
            getTags(log, tmp[0]);
            //后半段可以得到SQL
            getSQL(log, tmp[1]);
            logs.add(log);
        }
    }

    private static void getTags(LogStatement log, String info) {
        System.out.println("tags getTags：" + info);
        String[] tags = info.replace("eliteweb1 ","").replace("eliteweb2 ","").split(" ");
        System.out.println("tags length：" + tags.length);
        //用户
        log.setUser(tags[2]);
        //主机
        log.setHost(tags[4]);
        //Threadid
        log.setTheadId(tags[6]);
        //操作的数据库
        try {
            log.setSchema(tags[16]);
        }catch (Exception e){log.setSchema("-");}
        //查询时间
        log.setQueryTime(tags[8]);
        //执行成功后返回的行数
        log.setRow_sent(tags[12]);
        //检索的行数
        try {
        log.setRow_examined(tags[14]);
        }catch (Exception e){log.setRow_examined("-");}
    }

    private static void getSQL(LogStatement log, String statement) {
        //第一个分号后就是SQL，所以从第一个分号出现的位置+1分割字符串就可以得到SQL
        int subIndex = statement.indexOf(';');
        String sql = statement.substring(subIndex + 1).trim();    //提取SQL
        log.setSql(sql);
        getOrderFlag(log, sql);
    }

    private static void getOrderFlag(LogStatement log, String sql) { //提取部分SQL作为排序字段
        System.out.println("getOrderFlag 提取部分SQL作为排序字段：" + sql);
        /*sql = sql.toLowerCase();   //先转小写
        int index = sql.indexOf(";");   //先令sql分割点为末尾
        if (sql.startsWith(INSERT_STM)) {    //根据sql语句来定分割点
            index = !sql.contains("values") ? index : sql.indexOf("values");
        } else if (sql.startsWith(UPDATE_STM)) {
            index = !sql.contains("set") ? index : sql.indexOf("set");
        } else if (sql.startsWith(SELECT_STM)) {
            index = !sql.contains("where") ? index : sql.indexOf("where");
        }
        log.setOrderFlag(sql.substring(0, index));  //将截取后的sql语句设置为排序字段*/
        log.setOrderFlag("-");
    }

    private static void sortResult() {   // 排序方法
        logs.sort(new Comparator<LogStatement>() {
            @Override
            public int compare(LogStatement o1, LogStatement o2) {
                return o1.getQueryTime().compareTo(o2.getQueryTime());
            }
        });
    }

    private static void printResult() {  //打印结果
        System.out.println("慢总SQL条数：" + "\t" + totalSlowSQL);
        System.out.println();

        for (LogStatement log : logs) {
            System.out.println(log);
        }
    }
}